package com.zenithsun.news.newsinfo.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.springframework.stereotype.Repository;

import com.syzg.common.dao.impl.BaseDAOHibernate;
import com.syzg.core.util.StringUtil;
import com.zenithsun.news.newsinfo.dao.NewsinfoDAO;
import com.zenithsun.news.newsinfo.model.Newsinfo;
import com.zenithsun.news.specialnews.model.Specialnews;

@Repository("newsinfoDao")
public class NewsinfoDAOImpl extends BaseDAOHibernate<Newsinfo> implements NewsinfoDAO {
	
	/**
	 * countNewsinfo的查询SQL,获取审核通过的新闻
	 */
	private String SQL_countNewsinfo="SELECT COUNT(N.ID) as totalCount FROM NEWSINFO N,REVIEW R ";
	
	/**
	 * listNewsinfo的查询SQL,获取审核通过的新闻<br/>
	 * 优先按is_top（是否置顶）排序，其次按publish_time(发布时间)排序
	 */
	private String SQL_listNewsinfo="select (select count(nd.id) from newsdetailinfo nd where nd.new_id=n.id) as picNum,r.state,n.* from newsinfo n,review r";
	
	/**
	 * publishNews的SQL
	 */
	private String SQL_publishNews="UPDATE NEWSINFO N SET N.PUBLISH_TIME=? WHERE N.ID=?";
	
	/**
	 * updateCover的SQL
	 */
	private String SQL_updateCover="UPDATE NEWSINFO N SET N.COVER_PATH=? WHERE N.ID=?";
	
	@Override
	protected Class<Newsinfo> getEntityClass() {
		return Newsinfo.class;
	}

	@Override
	public void delNewsdetailinfo(String newsinfoId){
		executeSql(String.format("delete from newsdetailinfo where id='%s'",newsinfoId));
	}

	/**
	 * 获取条件,state等于-1则不适用状态作为条件
	 * @param searchCondition
	 * @return
	 */
	private String getCondition(String sql,Newsinfo searchCondition,int state){
		StringBuffer sb=new StringBuffer();
		sb.append(" WHERE N.ID=R.BUSID AND N.ISDELETE=0 ");
		if(state>-1){
			sb.append(" AND R.STATE= ").append(state);
		}
		if(StringUtils.isNotBlank(searchCondition.getId())){
			sb.append(" AND N.id ='").append(searchCondition.getId()).append("'");
		}
		if(searchCondition.getNewsColumns()!=null&&StringUtils.isNotBlank(searchCondition.getNewsColumns().getId())){
			sb.append(" AND N.NEWS_COLUMNS_ID='").append(searchCondition.getNewsColumns().getId()).append("'");
		}
		if(StringUtils.isNotBlank(searchCondition.getTitle())){
			sb.append(" AND N.TITLE like '%").append(searchCondition.getTitle()).append("%'");
		}
		if(StringUtils.isNotBlank(searchCondition.getType())){
			sb.append(" AND N.TYPE = '").append(searchCondition.getType()).append("'");
		}
		if(sb.length()==0){
			return "";
		}else{
			sb.insert(0, sql);
		}
		sb.append(" ORDER BY N.UPDATE_TIME DESC,N.IS_TOP DESC");
		return sb.toString();
	}
	
	/**
	 * 通过ID进行查询
	 * @param sql
	 * @param idArray
	 * @return
	 */
	private String getCondition(String sql,List<String> idArray){
		StringBuffer sb=new StringBuffer();
		sb.append(sql);
		sb.append(" WHERE N.ID=R.BUSID ");
		int index=0;
		if(idArray!=null&&idArray.size()>0){
			sb.append(" AND N.id in(");
			for(String id:idArray){
				sb.append("'").append(id).append("',");
			}
			sb.deleteCharAt(sb.lastIndexOf(","));
			sb.append(")");
		}
		return sb.toString();
	}
	
	@Override
	public List<Newsinfo> listNewsinfo(int pageNo, int pageSize,Newsinfo searchCondition,int state) throws Exception {
		SQLQuery query=getSession().createSQLQuery(getCondition(SQL_listNewsinfo,searchCondition,state));
		
		query.addEntity(getEntityClass());
		/*
		 * 设置分页条件
		 * 起始行数：(pageNo - 1) * pageSize
		 * 每页条数：pageSize
		 */
		query.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize);
		return query.list();
	}

	@Override
	public int countNewsinfo(Newsinfo searchCondition,int state) throws Exception {
		SQLQuery query=getSession().createSQLQuery(getCondition(SQL_countNewsinfo,searchCondition,state));
		Long count= Long.parseLong(query.uniqueResult().toString());
		return count.intValue();
	}

	@Override
	public boolean publishNews(Newsinfo newInfo) throws Exception {
		SQLQuery query=getSession().createSQLQuery(SQL_publishNews);
		query.setParameter(0, newInfo.getPublishTime());
		query.setParameter(1, newInfo.getId());
		return query.executeUpdate()>0;
	}

	@Override
	public boolean updateCover(String newsId, String coverPath) throws Exception {
		SQLQuery query=getSession().createSQLQuery(SQL_updateCover);
		query.setParameter(0,coverPath);
		query.setParameter(1,newsId);
		return query.executeUpdate()>0;
	}

	@Override
	public Newsinfo findNewsinfoById(String newsId) throws Exception {
		SQLQuery query=getSession().createSQLQuery(getCondition(SQL_listNewsinfo,new Newsinfo(newsId),-1));
		query.addEntity(getEntityClass());
		List list=query.list();
		if(list!=null&&list.size()>0){
			return (Newsinfo)list.get(0);
		}
		return null;
	}

	/**
	 * 获取删除的SQL语句
	 * @param tableName
	 * @param ids
	 * @return
	 */
	private String getDeleteBatchSQL(String tableName,String conId,String[] idArray){
		StringBuffer sb=new StringBuffer();
		sb.append("DELETE FROM ").append(tableName).append(" WHERE ").append(conId).append(" in (");
		if(idArray!=null&&idArray.length>0){
			int index=0;
			for(String item:idArray){
				sb.append("'").append(item).append("'");
				index++;
				if(index<=(idArray.length-1)){
					sb.append(",");
				}
			}
		}else{
			return null;
		}
		sb.append(")");
		return sb.toString();
	}
	
	/**
	 * 获取删除的SQL语句
	 * @param tableName
	 * @param ids
	 * @return
	 */
	private String getDeleteBatchSQLSolr(String tableName,String conId,String[] idArray){
		StringBuffer sb=new StringBuffer();
		sb.append("UPDATE ").append(tableName).append(" SET isDelete=1 WHERE ").append(conId).append(" in (");
		if(idArray!=null&&idArray.length>0){
			int index=0;
			for(String item:idArray){
				sb.append("'").append(item).append("'");
				index++;
				if(index<=(idArray.length-1)){
					sb.append(",");
				}
			}
		}else{
			return null;
		}
		sb.append(")");
		return sb.toString();
	}
	
	@Override
	public int deleteNewsinfoBatch(String[] newsids) {
		String delete=getDeleteBatchSQL("newsinfo","id",newsids);
		if(delete==null){
			return 0;
		}else{
			return executeSql(delete);
		}
	}

	@Override
	public int delNewsdetailinfoBatch(String[] newsids) {
		String delete=getDeleteBatchSQL("newsdetailinfo","New_id",newsids);
		if(delete==null){
			return 0;
		}else{
			return executeSql(delete);
		}
	}

	@Override
	public List<Newsinfo> listNewsinfo(List<String> idList) throws Exception {
		List<Newsinfo> list=null;
		if(idList==null||idList.size()==0){
			list=new ArrayList<Newsinfo>();
		}else{
			SQLQuery query=getSession().createSQLQuery(getCondition(SQL_listNewsinfo,idList));
			query.addEntity(getEntityClass());
			list=query.list();
		}
		return list;
	}
	/**
	 * 首页轮播
	 * @param rows 条数
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<HashMap<String, Object>> listNewsinfo_carousel(int rows) throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT N.id, N.title,N.content,N.cover_path,N.publish_time,R.state from review R left join newsinfo N on r.busId=N.id where R.state=3 and N.is_carousel=1 and isdelete=0 order by publish_time desc limit "+rows;
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		//执行查询
		List<HashMap<String, Object>> list =  query.list();
		// getSession().close();
		return list;
	}

	/**
	 * 置顶新闻
	 * @param rows 条数
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<HashMap<String, Object>> listNewsinfo_top(int rows)
			throws Exception {
		String sql = "select * from(select N.id, N.title,N.nabstract,N.content,N.cover_path,N.is_top,DATE_FORMAT(N.publish_time,'%Y%m%d%H%i%s') publish_time,DATE_FORMAT(N.publish_time,'%Y%m%d') y_m_d,DATE_FORMAT(N.publish_time,'%m') month_,DATE_FORMAT(N.publish_time,'%d') day_,R.state from review R left join newsinfo N on R.busId=N.id where R.state=3 AND R.bus_mark='News' and n.is_top=1 and isdelete=0) a where  not exists(select 1 from newsinfo where a.y_m_d = DATE_FORMAT(publish_time,'%Y%m%d') and a.publish_time<DATE_FORMAT(publish_time,'%Y%m%d%H%i%s')) order by a.publish_time desc limit "+rows;
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list =  query.list();
		return list;
	}
	/**
	 * 按主题查找新闻
	 * @param rows 行数
	 * @param columnId 主题ID
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<HashMap<String, Object>> listNewsinfo_columns(int rows, String columnId) throws Exception {
		// TODO Auto-generated method stub
		String sql = "SELECT N.id,N.title,N.content,N.nabstract,N.editor,N.photographer,N.cover_path,DATE_FORMAT(N.publish_time,'%Y-%m-%d') publish_time,N.news_columns_id columnId,R.state,N.page_view_counter FROM review R LEFT JOIN newsinfo N ON r.busId = N.id WHERE R.state = 3 and N.isdelete=0 ";
		if(StringUtils.isNotBlank(columnId)){
			sql+=" AND N.news_columns_id = '"+columnId+"'";
		}
		sql+=" order by publish_time desc limit "+rows;
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list = query.list();
		return list;
	}
	/**
	 * 最新新闻
	 * @param rows 行数
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<HashMap<String, Object>> listNewsinfo_upToDate(int rows)
			throws Exception {
		String sql = "SELECT N.id,N.title FROM review R RIGHT JOIN newsinfo N ON r.busId = N.id WHERE R.state = 3 and N.isdelete=0 order by page_view_counter desc limit " + rows;
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list = query.list();
		return list;
	}

	@Override
	public int deleteNewsinfoBatchSolr(String[] newsids) {
		String delete=getDeleteBatchSQLSolr("newsinfo","id",newsids);
		if(delete==null){
			return 0;
		}else{
			return executeSql(delete);
		}
	}
	@Override
	public boolean updateNewsinfoUpdateTimeSolr(String newsid) {
		SQLQuery query=getSession().createSQLQuery("UPDATE NEWSINFO SET UPDATE_TIME=? WHERE ID=?");
		query.setParameter(0, new Date());
		query.setParameter(1, newsid);
		return query.executeUpdate()>0;
	}

	/**
	 * 获取最新发布的新闻
	 * @param  limit 限制的条数
	 * @return 新闻集合
	 * @throws Exception
	 * */
	@Override
	public List<Newsinfo> getLatestPublishNews(int limit) {
		String sqlString = "select t1.*,0 as picNum,t2.state from newsinfo t1 left join review t2 on t2.busId = t1.id where t2.state = 3 and t1.isdelete=0 order by t1.publish_time limit " + limit;
		SQLQuery query=getSession().createSQLQuery(sqlString);
		query.addEntity(getEntityClass());
		return query.list();
	}
	

	/**
	 * 随机获取一条id不相等的图片新闻id
	 * @param  id 需要过滤的图片id
	 * @return 新闻的id
	 * @throws Exception
	 * */
	@Override
	public String getRandomNewsinfo(String id) {
		String sqlString = "select id from newsinfo where type='pic' and id <> '" + id + "' order by rand() limit 1";
		SQLQuery query=getSession().createSQLQuery(sqlString);
		return query.uniqueResult().toString();
	}
	
	/**
	 * 根据栏目id获取发布的新闻的数量
	 * @param  columnid 栏目的id
	 * @return 新闻的数量
	 * @throws Exception
	 * */
	@Override
	public int getPublishNewsCountByColumnid(String columnid){
		String sqlString = "select count(*) from newsinfo t1 left join review t2 on t2.busId = t1.id where t2.state = 3 and t1.isdelete=0 and news_columns_id = '" + columnid + "' order by t1.publish_time";
		SQLQuery query=getSession().createSQLQuery(sqlString);
		return Integer.parseInt(query.uniqueResult().toString());
	}
	
	/**
	 * 根据栏目id获取发布的新闻
	 * @param  pageNo 页码
	 * @param  limit 每页大小
	 * @param  columnid 栏目的id
	 * @return 新闻集合
	 * @throws Exception
	 * */
	@Override
	public List<Newsinfo> getPublishNewsByColumnid(int pageNo,int limit,String columnid){
		String sqlString = "select t1.*,0 as picNum,t2.state from newsinfo t1 left join review t2 on t2.busId = t1.id where t2.state = 3 and t1.isdelete=0 and news_columns_id = '" + columnid + "' order by t1.publish_time";
		SQLQuery query=getSession().createSQLQuery(sqlString);
		query.addEntity(getEntityClass());
		query.setFirstResult((pageNo - 1) * limit).setMaxResults(limit); //设置分页
		return query.list();
	}
	/**
	 * 根据批量ID查询
	 * @param ids
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Newsinfo> getNewsInIds(String[] ids,boolean isNull) {
		// TODO Auto-generated method stub
		String idsStr = "";
		for (int i = 0; i < ids.length; i++) {
			if(i == 0){
				idsStr += "(";
			}
			idsStr += "'"+ids[i]+"'";
			if(i < ids.length - 1){
				idsStr += ",";
			}else{
				idsStr += ")";
			}
		}
		StringBuffer sbSQL = new StringBuffer();
		sbSQL.append("select t1.*,0 as picNum,t2.state from newsinfo t1 left join review t2 on t2.busId = t1.id where t2.state = 3 and t1.isdelete=0 ");
		sbSQL.append("and t1.id in " + idsStr+" ");
		if(isNull){sbSQL.append("and t1.sn_id is null ");}
		sbSQL.append("order by t1.publish_time desc");
		SQLQuery query=getSession().createSQLQuery(sbSQL.toString());
		query.addEntity(getEntityClass());
		return query.list();
	}
	/**
	 * 专题ID查询新闻
	 * @param snId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Newsinfo> getNewsBySNID(String snId) {
		// TODO 获取专题新闻
		String sqlString = "select t1.*,0 as picNum,t2.state from newsinfo t1 left join review t2 on t2.busId = t1.id where t2.state = 3 and t1.sn_id = '"+snId+"' order by t1.publish_time desc";
		SQLQuery query=getSession().createSQLQuery(sqlString);
		query.addEntity(getEntityClass());
		return query.list();
	}
	/**
	 * 非专题新闻
	 * @param pageNo
	 * @param limit
	 * @param searchCondition
	 * @return
	 */
	@Override
	public int getNewsCountBySnIdIsNotNull(Newsinfo searchCondition) {
		// TODO Auto-generated method stub
		String sql = "select count(*) as totalCount from newsinfo N left join review t2 on t2.busId = N.id where t2.state = 3 and N.isdelete=0 and N.sn_id is null";
		SQLQuery query=getSession().createSQLQuery(sql);
		Long count= Long.parseLong(query.uniqueResult().toString());
		return count.intValue();
	}
	/**
	 * 非专题新闻 数量
	 */
	@Override
	public List<Newsinfo> getNewsListBySnIdIsNotNull(int pageNo, int limit,Newsinfo searchCondition) {
		// TODO Auto-generated method stub
//		try{
//			int columnsId=Integer.parseInt(searchCondition.getNewsColumns().getId());
//		}catch(NumberFormatException e){
//			e.printStackTrace();
//		}
		String sql = "select N.*,0 as picNum,t2.state from newsinfo N left join review t2 on t2.busId = N.id where t2.state = 3 and N.isdelete=0 and N.sn_id is null ";
		if(searchCondition.getNewsColumns()!=null){
		if(StringUtil.isNotEmpty(searchCondition.getNewsColumns().getId())){
			sql=sql+"and N.news_columns_id='"+searchCondition.getNewsColumns().getId()+"'";
			}
		}
	    if(StringUtil.isNotEmpty(searchCondition.getTitle())){
			sql=sql+"and N.title like'%"+searchCondition.getTitle()+"%'";
		}
		SQLQuery query=getSession().createSQLQuery(sql);
		query.addEntity(getEntityClass());
		return query.list();
	}
	/**
	 *最新的专题新闻
	 * @param rows 行数
	 * @return
	 */
	@Override
	public List<HashMap<String, Object>> getSpecialNewsUpToDate(int rows) {
		// TODO Auto-generated method stub
		String sql = "select * from specialnews order by create_time desc limit "+rows;
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<HashMap<String, Object>> list = query.list();
		return list;
	}
	
	/**
	 *专题新闻
	 * @return
	 */
	@Override
	public List<Specialnews> getSpecialNews(){
		String sql = "select * from specialnews order by create_time desc";
		SQLQuery query=getSession().createSQLQuery(sql);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<Specialnews> list = query.list();
		return list;
	}
}